import mysql,{OkPacket} from 'mysql'
import express, { Request } from 'express'
import boom, { boomify } from '@hapi/boom'
import { Result, ValidationError, validationResult } from 'express-validator'
import { querySql, newMysqlTrans } from '../db/mysql'
import { CODE_SUCCESS } from '../utils/constant'
const ARTICLE_TAG_SHIP= 'articletagship'
const ARTICLE_GALLERY = 'articlegallery'
const CATEGORY= 'category'
type I_articleData= {
    comment_nums: number,
    cover: string,
    createTime: Date,
    isVisible: 0|1,
    like_nums: number,
    md: string,
    read_nums: number,
    mdTitle: string,
    updateTime: Date
}
type I_category= {
    name: string,
    iconName: string,
    color: string
}
type I_articleDetail = I_articleData&I_category

export const getArticleList =function(req: express.Request, res: express.Response, next: express.NextFunction){
    const queryStr = `select id, comment_nums, cover,isVisible,like_nums,createTime,md,read_nums,mdTitle,updateTime,tags,name,iconName,color from ${ARTICLE_GALLERY} LEFT OUTER JOIN ${ARTICLE_TAG_SHIP} on articlegallery.id = articletagship.articleId LEFT JOIN ${CATEGORY} ON articletagship.categoryId = category.idcategory`
    querySql(queryStr).then((data:Array<I_articleDetail>) => {
        let mergeObj={}
        data.forEach(item => {
            const {comment_nums, cover, createTime, isVisible, like_nums,md, read_nums, mdTitle, updateTime,
                name, iconName, color}=item;
            // 如果已经存在  则直接插入数据
            if(mergeObj[item.mdTitle]){
                mergeObj[item.mdTitle].tags.push({
                    name, 
                    iconName, 
                    color
                })
                return
            }
            // 没有则存储 保存
            mergeObj[item.mdTitle] = {
                comment_nums, 
                cover, 
                createTime, 
                isVisible, 
                like_nums,
                md, 
                read_nums, 
                mdTitle, 
                updateTime,
                tags:[{
                    name, 
                    iconName, 
                    color
                }]
            }
        })
        data = Object.keys(mergeObj).map(keyName => {
            return mergeObj[keyName]
        })
        res.json({
            code: CODE_SUCCESS,
            data,
            msg:'成功'
        })
    }).catch(err => {
        const error= new Error(err)
        next(boom.boomify(error,{
            statusCode: 501,
            message: '数据库异常：'
        }))
    })

}

export const saveArticle = async function(req:express.Request, res:express.Response, next: express.NextFunction){
    const errors:Result<ValidationError> = validationResult(req)
    if(!errors.isEmpty()){
        const msgObj = errors.array({onlyFirstError: true})[0];
        const msg =  `缺失${msgObj.param},请检查`
        next(boom.badRequest(msg))
        return
    }
    if(!Array.isArray(req.body.tags)){
        const msg =  `tags数据类型错误,请检查`
        next(boom.badRequest(msg))
        return
    }
    let sqlArr:Array<any> = []
    req.body.createTime = new Date()
    req.body.updateTime = new Date()
    const keyArr =Object.keys(req.body)

    const itemValue = keyArr.map((keyName: string) => {
        if(keyName==='tags'){
            return req.body[keyName].join(',')
        }
        return req.body[keyName]
    })
    sqlArr.push(itemValue);

    const filedStr = keyArr.reduce((addKey: string, curKey: string, index:number) => {
        return index == keyArr.length-1 ? addKey+'`'+curKey+'`' : addKey+'`'+curKey+'`'+','
    }, '')

    try {
        
        await newMysqlTrans.trans(async (conn) => {
            // 保存文章
            const saveSql:string = `insert into ${ARTICLE_GALLERY}(${filedStr}) values ?`
            const articleRes: any = await newMysqlTrans.query(conn, saveSql, [sqlArr])
            
            const {insertId} = articleRes

            let shipArr:Array<Array<number>> =[]

            req.body.tags.forEach((val:number) => {
                shipArr.push([insertId, val])
            })

            // 批量插入
            const saveShipSql:string = "INSERT INTO articletagship(`articleId`,`categoryId`) VALUES ?"

            await newMysqlTrans.query(conn, saveShipSql, [shipArr])
           
        })
      
        res.json({
            code: CODE_SUCCESS,
            msg: '保存成功'
        })

       
    } catch(err) {
        const error= new Error(err)
        next(boom.boomify(error, {
            statusCode: 501,
            message: '数据库异常'
        }))
    }
}

// 更新
export async function updateArticle(req:express.Request, res:express.Response, next: express.NextFunction){
    const errors: Result<ValidationError> = validationResult(req)
    const {id} = req.params
    if(!errors.isEmpty()){
        const msgObj = errors.array({onlyFirstError: true})[0];
        const msg =  `缺失${msgObj.param},请检查`
        next(boom.badRequest(msg))
        return
    }
    if(!Array.isArray(req.body.tags)){
        const msg =  `tags数据类型错误,请检查`
        next(boom.badRequest(msg))
        return
    }

    req.body.updateTime = new Date()
    // req.body.cover = encodeURIComponent(req.body.cover)
    // req.body.md=encodeURIComponent(req.body.md)
    req.body.tags = req.body.tags.join(',')

    const keyArr =Object.keys(req.body)
    const updateArticleStr = keyArr.reduce((addkeyName, curKeyName, index) =>{
        return index ===0 ? addkeyName +  curKeyName + '=?' : addkeyName + ',' +  curKeyName + '=?' 
    },'')

    try {
        let updateSql = `UPDATE ${ARTICLE_GALLERY} SET ? WHERE id=${id}`
        await newMysqlTrans.trans(async(conn) => {
            await newMysqlTrans.query(conn, updateSql, req.body)
            const delSql = `DELETE FROM ${ARTICLE_TAG_SHIP} WHERE articleId=${id}`
            await newMysqlTrans.query(conn, delSql)
            // 批量插入
            const shipUpdateSql = `INSERT INTO ${ARTICLE_TAG_SHIP}(articleId,categoryId) VALUES ?`
            let shipArr =[]
            decodeURIComponent(req.body.tags).split(',').forEach((val: string) => {
                shipArr.push([id, val])
            })

            await newMysqlTrans.query(conn,shipUpdateSql, [shipArr])
        })
        res.json({
            code:CODE_SUCCESS,
            msg: '更新成功'
        })
   }catch(err) {
        const error= new Error(err)
        next(boom.boomify(error, {
            statusCode: 501,
            message: '数据库异常'
        }))
    }
    

}

export async function delArticel(req:express.Request, res: express.Response, next: express.NextFunction){
    const {id} = req.params
    if(id===undefined || id===null || id===''){
        next(boom.badRequest('缺少id，请检查'))
        return
    }
    try{
        await newMysqlTrans.trans(async(conn) => {
            await newMysqlTrans.query(conn, `DELETE FROM ${ARTICLE_GALLERY} WHERE id=${id}`)
            await newMysqlTrans.query(conn, `DELETE FROM ${ARTICLE_TAG_SHIP} WHERE articleId=${id}`)
        })
        res.json({
            code: CODE_SUCCESS,
            msg: '删除成功'
        })
    }catch(err){
        const errors = new Error(err)
        next(boom.boomify(errors, {
            statusCode: 501,
            message: '数据库异常：'
        }))
    };
}